from fastapi import APIRouter
# 在 user_api.py 文件中
from fastApi.database.base import get_db
import json
from datetime import datetime, timedelta

router = APIRouter()


@router.get("/analytics/sales-summary")
def get_sales_summary():
    """获取销售概览数据"""
    conn = get_db()

    # 获取总订单数
    conn.operation_sql("SELECT COUNT(*) FROM orders")
    conn.query.next()
    total_orders = conn.query.value(0)

    # 获取总销售额
    conn.operation_sql("SELECT SUM(price * quantity) FROM orders")
    conn.query.next()
    total_sales = conn.query.value(0) or 0

    # 获取总商品数量
    conn.operation_sql("SELECT SUM(quantity) FROM orders")
    conn.query.next()
    total_quantity = conn.query.value(0) or 0

    # 获取平均订单金额
    avg_order_value = total_sales / total_orders if total_orders > 0 else 0

    # 获取总商品种类数
    conn.operation_sql("SELECT COUNT(*) FROM products")
    conn.query.next()
    total_products = conn.query.value(0) or 0

    return {
        "total_orders": total_orders,
        "total_sales": round(total_sales, 2),
        "total_quantity": total_quantity,
        "avg_order_value": round(avg_order_value, 2),
        "total_products": total_products  # 添加总商品数
    }


@router.get("/analytics/sales-trend")
def get_sales_trend(days: int = 30):
    """获取销售趋势数据"""
    conn = get_db()

    # 获取最近N天的销售数据
    end_date = datetime.now()
    start_date = end_date - timedelta(days=days)

    # 按日期分组统计销售数据
    conn.operation_sql(f"""
        SELECT 
            order_date,
            COUNT(*) as order_count,
            SUM(price * quantity) as daily_sales,
            SUM(quantity) as daily_quantity
        FROM orders 
        WHERE order_date >= '{start_date.strftime('%Y-%m-%d')}'
        GROUP BY order_date
        ORDER BY order_date
    """)

    sales_data = []
    while conn.query.next():
        sales_data.append({
            "date": conn.query.value(0),
            "order_count": conn.query.value(1),
            "daily_sales": round(conn.query.value(2), 2),
            "daily_quantity": conn.query.value(3)
        })

    return sales_data


@router.get("/analytics/category-analysis")
def get_category_analysis():
    """获取分类分析数据"""
    conn = get_db()

    # 按分类统计销售数据
    conn.operation_sql("""
        SELECT 
            c.name as category_name,
            COUNT(DISTINCT o.product_id) as product_count,
            COUNT(o.id) as order_count,
            SUM(o.quantity) as total_quantity,
            SUM(o.price * o.quantity) as total_sales
        FROM orders o
        JOIN products p ON o.product_id = p.id
        LEFT JOIN categories c ON p.category_id = c.id
        GROUP BY c.id, c.name
        ORDER BY total_sales DESC
    """)

    categories_data = []
    while conn.query.next():
        categories_data.append({
            "category_name": conn.query.value(0),
            "product_count": conn.query.value(1),
            "order_count": conn.query.value(2),
            "total_quantity": conn.query.value(3),
            "total_sales": round(conn.query.value(4), 2)
        })

    return categories_data


@router.get("/analytics/customer-analysis")
def get_customer_analysis():
    """获取客户分析数据"""
    conn = get_db()

    # 获取客户消费排行
    conn.operation_sql("""
        SELECT 
            customer_name,
            customer_email,
            COUNT(*) as order_count,
            SUM(price * quantity) as total_spent,
            AVG(price * quantity) as avg_order_value,
            MIN(order_date) as first_order,
            MAX(order_date) as last_order
        FROM orders
        GROUP BY customer_name, customer_email
        ORDER BY total_spent DESC
        LIMIT 20
    """)

    customers_data = []
    while conn.query.next():
        customers_data.append({
            "customer_name": conn.query.value(0),
            "customer_email": conn.query.value(1),
            "order_count": conn.query.value(2),
            "total_spent": round(conn.query.value(3), 2),
            "avg_order_value": round(conn.query.value(4), 2),
            "first_order": conn.query.value(5),
            "last_order": conn.query.value(6)
        })

    return customers_data


@router.get("/analytics/monthly-stats")
def get_monthly_stats():
    """获取月度统计数据"""
    conn = get_db()

    # 按月份统计销售数据
    conn.operation_sql("""
        SELECT 
            strftime('%Y-%m', order_date) as month,
            COUNT(*) as order_count,
            SUM(price * quantity) as monthly_sales,
            SUM(quantity) as monthly_quantity,
            COUNT(DISTINCT customer_name) as unique_customers
        FROM orders
        GROUP BY strftime('%Y-%m', order_date)
        ORDER BY month DESC
        LIMIT 12
    """)

    monthly_data = []
    while conn.query.next():
        monthly_data.append({
            "month": conn.query.value(0),
            "order_count": conn.query.value(1),
            "monthly_sales": round(conn.query.value(2), 2),
            "monthly_quantity": conn.query.value(3),
            "unique_customers": conn.query.value(4)
        })

    return monthly_data


@router.get("/analytics/price-distribution")
def get_price_distribution():
    """获取价格分布数据"""
    conn = get_db()

    # 获取价格区间分布
    conn.operation_sql("""
        SELECT 
            CASE 
                WHEN o.price < 100 THEN '0-100'
                WHEN o.price < 500 THEN '100-500'
                WHEN o.price < 1000 THEN '500-1000'
                WHEN o.price < 5000 THEN '1000-5000'
                ELSE '5000+'
            END as price_range,
            COUNT(*) as order_count,
            SUM(o.quantity) as total_quantity
        FROM orders o
        GROUP BY price_range
        ORDER BY 
            CASE price_range
                WHEN '0-100' THEN 1
                WHEN '100-500' THEN 2
                WHEN '500-1000' THEN 3
                WHEN '1000-5000' THEN 4
                ELSE 5
            END
    """)

    price_data = []
    while conn.query.next():
        price_data.append({
            "price_range": conn.query.value(0),
            "order_count": conn.query.value(1),
            "total_quantity": conn.query.value(2)
        })

    return price_data